/*
 * FileName:    　　　QueryUtils.java
 * Description:  検索条件の処理
 * History:     2010-8-24 (廖宝俊) 1.0 Create
 */
package com.sn.system.common.utils;

import java.util.Date;

import com.sn.system.common.constants.Constants;



public final class QueryUtils {

    private static String[] andConditionArray;

    private static String[] orConditionArray;

    private static int CONDITION_FIRST = 0;

    public static String CONDITION_PREFIX = "CONDITION_";

    /** イコール計算の定数 */
    public static int CONDITION_EQU = CONDITION_FIRST;

    /** 大なり計算の定数 */
    public static int CONDITION_GT = CONDITION_EQU + 1;

    /** 大なりイコール計算の定数 */
    public static int CONDITION_GT_EQU = CONDITION_GT + 1;

    /** 小なり計算の定数 */
    public static int CONDITION_LESS = CONDITION_GT_EQU + 1;

    /** 小なりイコールの定数 */
    public static int CONDITION_LESS_EQU = CONDITION_LESS + 1;

    /** like計算の定数 */
    public static int CONDITION_LIKE = CONDITION_LESS_EQU + 1;
    
    /** 後方一致like計算の定数 */
    public static int CONDITION_LIKE_LEFT = CONDITION_LIKE + 1;
    
    /** 前方一致like計算の定数 */
    public static int CONDITION_LIKE_RIGHT = CONDITION_LIKE_LEFT + 1;

    /** イコールならず計算の定数 */
    public static int CONDITION_NOT_EQU = CONDITION_LIKE_RIGHT + 1;

    /** IN計算の定数 */
    public static int CONDITION_IN = CONDITION_NOT_EQU + 1;

    private static int CONDITION_LAST = CONDITION_IN;

    static {
        andConditionArray = new String[CONDITION_LAST + 1];
        andConditionArray[CONDITION_EQU] = " = ? and";
        andConditionArray[CONDITION_GT] = " > ? and";
        andConditionArray[CONDITION_GT_EQU] = " >= ? and";
        andConditionArray[CONDITION_LESS] = " < ? and";
        andConditionArray[CONDITION_LESS_EQU] = " <= ? and";
        andConditionArray[CONDITION_LIKE] = " like ? and";
        andConditionArray[CONDITION_LIKE_LEFT] = " like ? and";
        andConditionArray[CONDITION_LIKE_RIGHT] = " like ? and";
        andConditionArray[CONDITION_NOT_EQU] = " <> ? and";
        andConditionArray[CONDITION_IN] = " in (?) and";

        orConditionArray = new String[CONDITION_LAST + 1];
        orConditionArray[CONDITION_EQU] = " = ? or";
        orConditionArray[CONDITION_GT] = " > ? or";
        orConditionArray[CONDITION_GT_EQU] = " >= ? or";
        orConditionArray[CONDITION_LESS] = " < ? or";
        orConditionArray[CONDITION_LESS_EQU] = " <= ? or";
        orConditionArray[CONDITION_LIKE] = " like ? or";
        orConditionArray[CONDITION_LIKE_LEFT] = " like ? or";
        orConditionArray[CONDITION_LIKE_RIGHT] = " like ? or";
        orConditionArray[CONDITION_NOT_EQU] = " <> ? or";
        orConditionArray[CONDITION_IN] = " in (?) or";
    }

    /**
     * コンストラクタ
     */
    private QueryUtils() {
        // プライベート
    }

    /**
     * 計算条件定数の合法性を判断する
     */
    private static void checkForConditionRange(int condition) {
        if (condition < CONDITION_FIRST || condition > CONDITION_LAST) {
            throw new RuntimeException("計算条件定数違法！当面の値は[" + new Integer(condition) + "], 合法値の範囲は["
                            + new Integer(CONDITION_FIRST) + ".." + new Integer(CONDITION_LAST)
                            + "]");
        }
    }

    /**
     * 　like文のobject中の"["、"]"、"_"、"%"を"[[]"、"[]]"、"[_]"、"[%]"に替えます
     * @param object like文のobject対象
     * @return 交替した後の文字列
     */
    public static String convertSqlLikePattern(Object object) {
        if (object != null) {
            String result = object.toString();
            if ( (result != null) && (result.length() > 0) &&
                    (result.indexOf("[") >= 0
                            || result.indexOf("]") >= 0
                            || result.indexOf("_") >= 0
                            || result.indexOf("%") >= 0
                    )
               ) {
                return result.replaceAll("([\\[\\]%_])", "[$1]");
            }
            return result;
        }
        return "";
    }

    /**
     * like文のobject文字列を処理する
     * @param object 文字列
     * @return 処理した後の文字列
     *
     */
    public static String convertObjectToMultiKeyWord(Object object) {
        if (object != null) {
            String result = object.toString();
            if ( (result != null) && (result.length() > 0) &&
                    (result.indexOf(" ") >= 0
                    )
               ) {
                return result.replaceAll("([ ]+)", "%");
            }
            return result;
        }
        return "";
    }

    /**
     * conditionArrayによると、対応する単一のクエリによって生成された
     *
     * @param valueObject 		検索の条件の値
     * @param sqlPropertyName 	データテーブルの列名
     * @param valueIndex 		valuesアレイ当面のindex
     * @param values 			検索の条件の値のアレイ
     * @param whereClause 		検索の文のStringBuffer
     * @param condition 		計算条件マーク，QueryUtilsでCONDITION_XXXXに記録します
     * @param conditionArray 	計算条件マークのアレイ
     * @return valuesアレイ次のindex
     */
    private static int buildConditionCell(Object valueObject, String sqlPropertyName, int valueIndex,
            Object[] values, StringBuffer whereClause, int condition, final String[] conditionArray) {
        if (valueObject == null) {
            return valueIndex;
        }
        checkForConditionRange(condition);
        Class objectClass = valueObject.getClass();
        if (String.class.isAssignableFrom(objectClass)) {
            if (((String) valueObject).length() > 0) {
                whereClause.append(" ").append(sqlPropertyName).append(conditionArray[condition]);
                if (condition == CONDITION_LIKE) {
                    values[valueIndex++] = "%" + convertObjectToMultiKeyWord(convertSqlLikePattern(valueObject)) + "%";
                }else if (condition == CONDITION_LIKE_LEFT) {
                    values[valueIndex++] = "%" + convertObjectToMultiKeyWord(convertSqlLikePattern(valueObject));
                } else if (condition == CONDITION_LIKE_RIGHT) {
                    values[valueIndex++] = convertObjectToMultiKeyWord(convertSqlLikePattern(valueObject)) + "%";
                } else {
                    values[valueIndex++] = valueObject;
                }
            }
        } else if (Number.class.isAssignableFrom(objectClass)) {
            whereClause.append(" ").append(sqlPropertyName).append(conditionArray[condition]);
            values[valueIndex++] = valueObject;
        } else if (Date.class.isAssignableFrom(objectClass)) {
          whereClause.append(" to_char(").append(sqlPropertyName).append(", 'YYYY/MM/DD')")
          .append(conditionArray[condition]);
          values[valueIndex++] = Constants.DATE_FORMAT2.format(valueObject);

//            whereClause.append(" convert(char(10), ").append(sqlPropertyName).append(", 111)")
//            .append(conditionArray[condition]);
//            values[valueIndex++] = Constants.DATE_FORMAT2.format(valueObject);

        }else if (valueObject instanceof java.util.List) {
        	if(((java.util.List)valueObject).size()>0&&((java.util.List)valueObject).get(0)!=null){
        		 whereClause.append(" ").append(sqlPropertyName).append(conditionArray[condition].replace("?", ":inList"+valueIndex));
                 values[valueIndex++] = valueObject;
        	}
        }
        return valueIndex;
    }

    /**
     * 単一のクエリによると、検索の文によって生成された
     * @param valueObject 		検索の条件の値
     * @param sqlPropertyName 	データテーブルの列名
     * @param valueIndex 		valuesアレイ当面のindex
     * @param values 			検索の条件の値のアレイ
     * @param whereClause 		検索の文のStringBuffer
     * @param condition 		計算条件マーク，QueryUtilsでCONDITION_XXXXに記録します
     * @return valuesアレイ次のindex
     */
    public static int buildOneCondition(Object valueObject, String sqlPropertyName, int valueIndex,
                    Object[] values, StringBuffer whereClause, int condition) {
        if (valueObject == null) {
            return valueIndex;
        }
        checkForConditionRange(condition);
        Class objectClass = valueObject.getClass();
        if (objectClass.isArray()) {
            Object [] objectArray = (Object []) valueObject;
            if (objectArray.length > 0) {
                StringBuffer orSql = new StringBuffer(100);
                for (int i = 0; i < objectArray.length; i++) {
                    valueIndex = buildConditionCell(objectArray[i], sqlPropertyName, valueIndex,
                            values, orSql, condition, orConditionArray);
                }
                if (orSql.length() > 0) {
                    orSql = orSql.delete(orSql.length() - 2, orSql.length());//最後のorを削除する
                    whereClause.append(" (").append(orSql).append(") and");
                }
            }
        } else {
            valueIndex = buildConditionCell(valueObject, sqlPropertyName, valueIndex,
                    values, whereClause, condition, andConditionArray);
        }
        return valueIndex;
    }

    /**
     * 検索の条件対象生成する
     * @param sqlPropertyName 	データテーブルの列名
     * @param condition 		計算条件マーク，QueryUtilsでCONDITION_XXXXに記録します
     * @param valueObject 		検索の条件の値
     * @return Condition　検索の条件対象
     */
    public static Condition buildOneCondition(String sqlPropertyName, int condition, Object valueObject) {
        if (valueObject == null) {
            return new Condition("", new Object[0]);
        }
        int valueIndex = 0;
        Object[] values = new Object[50]; // 50の条件は极限です
        StringBuffer whereClause = new StringBuffer(100);

        checkForConditionRange(condition);
        Class objectClass = valueObject.getClass();
        if (objectClass.isArray()) {
            Object [] objectArray = (Object []) valueObject;
            if (objectArray.length > 0) {
                StringBuffer orSql = new StringBuffer(100);
                for (int i = 0; i < objectArray.length; i++) {
                    valueIndex = buildConditionCell(objectArray[i], sqlPropertyName, valueIndex,
                            values, orSql, condition, orConditionArray);
                }
                if (orSql.length() > 0) {
                    orSql = orSql.delete(orSql.length() - 2, orSql.length());//最後のorを削除する
                    whereClause.append(" (").append(orSql).append(") and");
                }
            }
        } else {
            valueIndex = buildConditionCell(valueObject, sqlPropertyName, valueIndex,
                    values, whereClause, condition, andConditionArray);
        }
        Object[] nowCondition = new Object[valueIndex];
        System.arraycopy(values, 0, nowCondition, 0, valueIndex);
        //whereClause.delete(whereClause.length() - 3, whereClause.length());
        return new Condition(whereClause.toString(), nowCondition);
    }

    /**
     * convertSqlLikePattern方法のテスト
     */
    public static void main(String[] args) {
        String s = "[[[1]]]2%3_4";
        System.out.print(s + " = ");
        System.out.println(convertSqlLikePattern(s));

        s = "1]]]2%3_4";
        System.out.print(s + " = ");
        System.out.println(convertSqlLikePattern(s));

        s = "1]]]2 3_4";
        System.out.print(s + " = ");
        System.out.println(convertSqlLikePattern(s));

        s = "1]]]2 3 4";
        System.out.print(s + " = ");
        System.out.println(convertSqlLikePattern(s));

        s = "1   2 3 4";
        System.out.print(s + " = ");
        System.out.println(convertSqlLikePattern(s));

        s = "1234   23 34 45     a";
        System.out.print(s + " = ");
        System.out.println(convertObjectToMultiKeyWord(s));
    }

    /**
     * 検索の条件対象
     */
    protected static class Condition implements ICondition {

        /** 検索の条件 */
        final String conditionString;

        /** 検索の条件の値のアレイ */
        final Object[] param;

        public Condition(final String conditionString, final Object[] param) {
            this.conditionString = conditionString;
            this.param = param;
        }

        /**
         * @see com.spower.utils.ICondition#getConditionString()
         */
        public String getConditionString() {
            return this.conditionString;
        }

        /**
         * @see com.spower.utils.ICondition#getParam()
         */
        public Object[] getParam() {
            return this.param;
        }

    }

}
